package com.mingda.weathermagiccenter.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Mapper
public interface CommMapper extends BaseMapper {



    @Select("""  
            WITH input_point AS ( 
                SELECT ST_SetSRID(ST_MakePoint(#{lon},#{lat}), 4326) AS geom 
            ) 
            SELECT 
                pf.id, 
           /*
                 ST_AsText(pf.the_geom) as geom,
                    ST_AsGeoJSON (ST_Simplify(pf.the_geom, 0.0002))  AS geojson,*/
                CASE 
                    WHEN ST_Contains(pf.the_geom, input_point.geom) THEN 
                        -ST_Distance( 
                            ST_Transform(input_point.geom, 3857),
                            ST_Transform(ST_ClosestPoint(ST_ExteriorRing(pf.the_geom), input_point.geom), 3857)  
                        )  
                    ELSE 
                        ST_Distance( 
                            ST_Transform(input_point.geom, 3857), 
                            ST_Transform(ST_ClosestPoint(pf.the_geom, input_point.geom), 3857) 
                        )  
                END AS distance_m  
            		,   ST_AsText(ST_ClosestPoint(ST_ExteriorRing(pf.the_geom), input_point.geom)) AS closest_point 
            FROM 
                pf_land_class_area pf, 
                input_point 
            		where pf.main_type = '林地'
            ORDER BY 
                distance_m
            LIMIT 1;   
            """)
    List<Map<String, Object>>   getDistance(@Param("lon") Double lon, @Param("lat") Double lat);


    @Select({
            "<script>",
            "SELECT a.stationKey, a.orgid, a.orgname, b.prec, a.stationName, a.stationtype",
            "FROM ws_station a, (",
            "    SELECT stationKey, SUM(prec) AS prec",
            "    FROM ws_station_data",
            "    WHERE 1 = 1",
            "    AND stationKey = #{stationKey}",
            "    AND TO_TIMESTAMP(REPLACE(#{btime}, 'T', ' '), 'YYYY-MM-DD HH24:MI:SS') &lt;= timeBase",
            "    AND TO_TIMESTAMP(REPLACE(#{etime}, 'T', ' '), 'YYYY-MM-DD HH24:MI:SS') &gt;= timeBase",
            "    AND prec  &lt; 100",
            "    GROUP BY stationKey",
            ") AS b",
            "WHERE a.stationKey = b.stationKey",
            "</script>"
    })
    List<Map<String, Object>>  getPec24(@Param("stationKey") String stationKey,
                                        @Param("btime") String btime,
                                        @Param("etime") String etime);
    

}
